#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time   : 2021/7/14 17:00
# @Author : cjw
"""
处理事务和DBAPI
"""
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

# 建立连接引擎
# 使用pysqlite驱动连接内存数据库
# future=True： 充分利用 2.0 style 用途
# echo=True：显示所有执行的SQL语句
# 惰性连接，只有在第一次要求它对数据库执行任务时才会和数据库建立连接
engine = create_engine('sqlite+pysqlite:///:memory:', echo=True, future=True)

# 获取连接：使用上下文
# connect(): 和数据库建立连接
# text(): 将SQL语句编写为 文本SQL
# execute(): 执行sql语句，返回执行结果
with engine.connect() as conn:
    result = conn.execute(text('select "hello world!"'))
    print(result.all())  # all(): 获取所有行结果

# 提交更改：DBAPI连接是非自动提交的，需要手动提交更改
with engine.connect() as conn:
    conn.execute(text('create table some_table(x int, y int)'))
    conn.execute(
        text('insert into some_table (x,y) values (:x, :y)'),
        [{'x': 1, 'y': 1}, {'x': 2, 'y': 4}]
    )
    conn.commit()

# begin(): 声明事务块连接，在块级作用域内最后会自动提交，提交异常会回滚
with engine.begin() as conn:
    conn.execute(
        text('insert into some_table (x,y) values (:x, :y)'),
        [{'x': 6, 'y': 8}, {'x': 9, 'y': 10}]
    )

# 语句执行基础
# 获取行
with engine.connect() as conn:
    result = conn.execute(text('select x,y from some_table'))
    for row in result:
        print(f'x: {row.x}    y: {row.y}')

# 发送参数：命名了一个名为“y”的新参数 text() 构造使用冒号格式“:y”接受这些内容
with engine.connect() as conn:
    # execute()执行sql的结果，是一个可迭代的对象
    result = conn.execute(
        text('select x, y from some_table where y > :y'),
        [{'y': 2}]
    )
    for row in result:
        print(f'x: {row.x}  y: {row.y}')

# 将参数与语句绑定
stmt = text('select x, y from some_table where y > :y order by x, y').bindparams(y=6)
with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f'x: {row.x}    y: {row.y}')

# 使用ORM会话执行
stmt = text('select x, y from some_table where y > :y order by x, y').bindparams(y=6)
with Session(engine) as session:
    result = session.execute(stmt)
    for row in result:
        print(f'x: {row.x}  y: {row.y}')
